<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * @author gilmario
 */

namespace App\Models;

use SON\DB\Table;

class Conta extends Table {

    protected $table = 'conta';

    public function adiciona($num_doc, $descricao, $beneficiario, $valor, $vencimento, $status) {
        try {
            $this->db->beginTransaction();
            $query = "INSERT INTO {$this->table}(numero_documento,descricao,beneficiario,valor,vencimento,data_cadastro,data_baixa, status) values"
                    . " ('{$num_doc}', '{$descricao}', {$beneficiario}, $valor, '{$vencimento}', now(), '', '{$status}') ";
            $this->db->query($query);
            $this->db->commit();
        } catch (Exception $ex) {
            //echo $ex;
            $this->db->roolBack();
        }
    }

    public function atualiza($num_doc, $descricao, $beneficiario, $valor, $vencimento, $id) {
        try {
            $this->db->beginTransaction();
            $query = "UPDATE {$this->table} set numero_documento='{$num_doc}',descricao='{$descricao}',beneficiario={$beneficiario},valor={$valor},vencimento='{$vencimento}' WHERE id={$id}";
            $this->db->query($query);
            $this->db->commit();
        } catch (Exception $ex) {
            $this->db->roolBack();
        }
    }

    public function finaliza($id) {
        try {
            $this->db->beginTransaction();
            $query = "UPDATE {$this->table} SET status='Paga' WHERE id={$id}";
            $this->db->query($query);
            $this->db->commit();
        } catch (Exception $ex) {
            echo $ex;
            $this->db->roolBack();
        }
    }

    public function consultaBeneficiario($beneficiario) {
        $query = "SELECT conta.*, beneficiario.nome as beneficiario_nome FROM {$this->table}, beneficiario WHERE conta.beneficiario = beneficiario.id AND beneficiario.nome like '{$beneficiario}%' AND conta.status='Aberto' ";
        return $this->db->query($query);
    }

    public function listarPeriodo($dataini, $datafim) {
        $query = "SELECT conta.*, beneficiario.nome as beneficiario_nome FROM {$this->table}, beneficiario WHERE conta.beneficiario = beneficiario.id  AND conta.vencimento BETWEEN '{$dataini}' AND '{$datafim}' ORDER BY conta.vencimento";
        return $this->db->query($query);
    }

    public function listarVencidos() {
        $query = "SELECT conta.*, beneficiario.nome as beneficiario_nome FROM {$this->table}, beneficiario WHERE conta.beneficiario = beneficiario.id  AND conta.vencimento < now() AND conta.status='Aberto' ORDER BY conta.vencimento";
        return $this->db->query($query);
    }

    public function balanco() {
        $query = "select month(vencimento) as mes, SUM(valor) as total from conta WHERE year(vencimento) = 2013 group by month(vencimento)  order by month(vencimento);";
        return $this->db->query($query);
    }

    public function maiorBeneficiario($mes) {
        $query = "select beneficiario.nome as beneficiario, SUM(valor) as total from conta, beneficiario WHERE beneficiario.id = conta.beneficiario AND month(vencimento) = {$mes} group by beneficiario.nome order by total desc";
        $rows = $this->db->query($query)->fetch();

        return $rows[0];
    }

    public function baixa($id, $valor) {
        // Inserir parcela
        $daoP = \SON\DI\Container::getClass('parcela');
        $daoP->adiciona($id, $valor);
        $valorPago = $daoP->valorPago($id);
        $conta = $this->carregar("id", $id);
        if ($conta['valor'] <= $valorPago) {
            $this->finaliza($id);
            return true;
        } else {
            return false;
        }
    }

    public function excluir($id) {
        try {
            $this->db->beginTransaction();
            $query = "DELETE FROM {$this->table} WHERE id={$id}";
            $this->db->query($query);
            $this->db->commit();
        } catch (Exception $ex) {
            echo $ex;
            $this->db->roolBack();
        }
    }

    protected function create() {

        try {
            $this->db->beginTransaction();
            $query = "CREATE TABLE IF NOT EXISTS {$this->table}(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, numero_documento varchar(40), descricao varchar(255), beneficiario Integer not null, valor double, vencimento date not null, data_cadastro datetime not null, data_baixa date, status varchar(10), foreign key (beneficiario) references beneficiario(id) ) ENGINE MYISAM";
            $this->db->query($query);
            $this->db->commit();
        } catch (Exception $ex) {
            echo $ex;
            $this->db->roolBack();
        }
    }

}
